﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public class CMTJDAL
    {

        /// <summary>
        /// 获取保险流水
        /// </summary>
        /// <param name="InsuranceDateStart"></param>
        /// <param name="InsuranceDateEnd"></param>
        /// <param name="zcenterCode"></param>
        /// <param name="zstationCode"></param>
        /// <returns></returns>
        public DataTable GetLSBaoXian(DateTime InsuranceDateStart, DateTime InsuranceDateEnd, string zcenterCode, string zstationCode, string zPlateNumber)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(@"  
               select  ca.PlateNumber,   
                        MONTH(ci.InsuranceDate)  as Months 
                        ,ts.名称  as tsName
                        ,ci.InsuranceExpireDate
                        ,ca.EnableDate
                        ,ca.Remark

                        from C_Insurance   ci 
                        left join C_Ambulance ca on  ci.AmbulanceCode = ca.AmbulanceCode  ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append("  left join  ").Append(builder.InitialCatalog).Append(".dbo.TStation  ts on ca.StationCode = ts.编码 ");
            sb.Append(@" where 1 = 1  ");

            WhereClauseUtility.AddDateTimeGreaterThan("ci.InsuranceExpireDate", InsuranceDateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("ci.InsuranceExpireDate", InsuranceDateEnd, sb);
            WhereClauseUtility.AddInSelectQuery("ca.CenterCode", zcenterCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.StationCode", zstationCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.AmbulanceCode", zPlateNumber, sb);

            sb.Append("   and  ca.isActive = 1 and ts.是否有效=1  ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取年检流水
        /// </summary>
        /// <param name="AnnualSurveyDateExpireStart"></param>
        /// <param name="AnnualSurveyDateExpireEnd"></param>
        /// <param name="zcenterCode"></param>
        /// <param name="zstationCode"></param>
        /// <param name="zPlateNumber"></param>
        /// <returns></returns>
        public DataTable GetLSNianJian(DateTime AnnualSurveyDateExpireStart, DateTime AnnualSurveyDateExpireEnd, string zcenterCode, string zstationCode, string zPlateNumber)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(@"  

                            select 
                     ca.PlateNumber  

                    ,ts.名称  as  tsName
                    ,(tdb.Name +tdu.Name+'车') as caName

                    ,cas.AnnualSurveyExpireDate
                    ,ca.InsuranceExpireDate
                    ,ca.EnableDate
                    ,ca.Remark

                    from C_AnnualSurvey   cas 
                    left join C_Ambulance ca on  cas.AmbulanceCode = ca.AmbulanceCode ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append("  left join  ").Append(builder.InitialCatalog).Append(".dbo.TStation  ts on ca.StationCode = ts.编码 ");

            sb.Append(@"
                    left join TDictionary tdb on ca.Brand = tdb.ID
                    left join TDictionary tdu on ca.UseFor  = tdu.ID

                    where   1=1 ");

            WhereClauseUtility.AddDateTimeGreaterThan("cas.AnnualSurveyExpireDate", AnnualSurveyDateExpireStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("cas.AnnualSurveyExpireDate", AnnualSurveyDateExpireEnd, sb);
            WhereClauseUtility.AddInSelectQuery("ca.CenterCode", zcenterCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.StationCode", zstationCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.AmbulanceCode", zPlateNumber, sb);

            sb.Append(@"
                  and  ca.isActive = 1 and ts.是否有效=1 ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];

        }


        /// <summary>
        /// 获取流水报废
        /// </summary>
        /// <param name="RejectStart"></param>
        /// <param name="RejectEnd"></param>
        /// <param name="zcenterCode"></param>
        /// <param name="zstationCode"></param>
        /// <returns></returns>
        public DataTable GetLSBaoFei(DateTime RejectStart, DateTime RejectEnd, string zcenterCode, string zstationCode)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        select ca.PlateNumber 
                        ,(tdb.Name +tdu.Name+'车') as caName
                        ,tdt.Name  as TypeName
                        ,ca.Model
                        ,ca.EngineNumber
                        ,Number = 1
                        ,ca.CarNumber
                        ,ca.Price
                        ,ca.Remark
						,ca.RejectedDate

                   from C_Ambulance ca  
                   left join TDictionary tdt on ca.Type =tdt.ID
                   left join TDictionary tdb on ca.Brand = tdb.ID
                left join TDictionary tdu on ca.UseFor  = tdu.ID  
                    where ca.isRejected = 1  ");
            WhereClauseUtility.AddDateTimeGreaterThan("ca.RejectedDate", RejectStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("ca.RejectedDate", RejectEnd, sb);
            WhereClauseUtility.AddInSelectQuery("ca.CenterCode", zcenterCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.StationCode", zstationCode, sb);

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取事故流水
        /// </summary>
        /// <param name="accidentStart"></param>
        /// <param name="accidentEnd"></param>
        /// <param name="zcenterCode"></param>
        /// <param name="zstationCode"></param>
        /// <param name="zPlateNumber"></param>
        /// <param name="accidentLevel"></param>
        /// <param name="accidentRespons"></param>
        /// <returns></returns>
        public DataTable GetLSShiGu(DateTime accidentStart, DateTime accidentEnd, string zcenterCode, string zstationCode, string zPlateNumber)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                       select  ts.名称 as tsName
                        ,ca.PlateNumber
                        ,cad.AccidentTime
                        ,cad.AccidentAddress
                        ,tdal.Name  as Level  
                        ,tdr.Name  as  Respons  
                        ,cad.ReportNumber 
                        ,cad.Description 
                        ,cad.DealExplain 
                        ,cad.InsuranceExplain 
                        ,cad.Remark 
                            from C_Accident cad  
                        left join C_Ambulance ca on ca.AmbulanceCode  = cad.AmbulanceCode ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append("  left join  ").Append(builder.InitialCatalog).Append(".dbo.TStation  ts on ca.StationCode = ts.编码 ");
            sb.Append(@"
                        left join TDictionary tdal on tdal.ID = cad.AccidentLevel
                        left join TDictionary tdr on tdr.ID = cad.Responsibility  
                        where 1=  1  ");
            WhereClauseUtility.AddDateTimeGreaterThan("cad.AccidentTime", accidentStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("cad.AccidentTime", accidentEnd, sb);
            WhereClauseUtility.AddInSelectQuery("ca.CenterCode", zcenterCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.StationCode", zstationCode, sb);
            WhereClauseUtility.AddInSelectQuery("ca.AmbulanceCode", zPlateNumber, sb);

            sb.Append("   and  ca.isActive = 1 and ts.是否有效=1  ");


            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }


        #region 车管科保养计划安排表
        //获取整年保养的明细
        public DataTable GETYearCGKMaintainPlan(string ambNumber, string year)
        {
            //共72列
            DataTable total = new DataTable();
            total.Columns.Add("A");
            total.Columns.Add("B");
            total.Columns.Add("C");
            total.Columns.Add("D");
            total.Columns.Add("E");
            total.Columns.Add("F");
            total.Columns.Add("G");
            total.Columns.Add("H");
            total.Columns.Add("I");
            total.Columns.Add("J");
            total.Columns.Add("K");
            total.Columns.Add("L");
            total.Columns.Add("M");
            total.Columns.Add("N");
            total.Columns.Add("O");
            total.Columns.Add("P");
            total.Columns.Add("Q");
            total.Columns.Add("R");
            total.Columns.Add("S");
            total.Columns.Add("T");
            total.Columns.Add("U");
            total.Columns.Add("V");
            total.Columns.Add("W");
            total.Columns.Add("X");
            total.Columns.Add("Y");
            total.Columns.Add("Z");

            total.Columns.Add("A1");
            total.Columns.Add("B1");
            total.Columns.Add("C1");
            total.Columns.Add("D1");
            total.Columns.Add("E1");
            total.Columns.Add("F1");
            total.Columns.Add("G1");
            total.Columns.Add("H1");
            total.Columns.Add("I1");
            total.Columns.Add("J1");
            total.Columns.Add("K1");
            total.Columns.Add("L1");
            total.Columns.Add("M1");
            total.Columns.Add("N1");
            total.Columns.Add("O1");
            total.Columns.Add("P1");
            total.Columns.Add("Q1");
            total.Columns.Add("R1");
            total.Columns.Add("S1");
            total.Columns.Add("T1");
            total.Columns.Add("U1");
            total.Columns.Add("V1");
            total.Columns.Add("W1");
            total.Columns.Add("X1");
            total.Columns.Add("Y1");
            total.Columns.Add("Z1");

            total.Columns.Add("A2");
            total.Columns.Add("B2");
            total.Columns.Add("C2");
            total.Columns.Add("D2");
            total.Columns.Add("E2");
            total.Columns.Add("F2");
            total.Columns.Add("G2");
            total.Columns.Add("H2");
            total.Columns.Add("I2");
            total.Columns.Add("J2");
            total.Columns.Add("K2");
            total.Columns.Add("L2");
            total.Columns.Add("M2");
            total.Columns.Add("N2");
            total.Columns.Add("O2");
            total.Columns.Add("P2");
            total.Columns.Add("Q2");
            total.Columns.Add("R2");
            total.Columns.Add("S2");
            total.Columns.Add("T2");

            //先确定有多少行，将行数 31
            for (int i1 = 0; i1 < 31; i1++)
            {
                DataRow dr = total.NewRow();
                //列 12 
                for (int i = 1; i < 13; i++)
                {
                    string ii = i.ToString();
                    DataTable temp = getCGKMaintainPlan(ambNumber, year, ii);
                    if (temp.Rows.Count > 0)
                    {
                        if (i == 1)
                        {
                            dr["A"] = temp.Rows[i1]["日"].ToString();
                            dr["B"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["C"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["D"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["E"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["F"] = temp.Rows[i1]["总里程"].ToString();
                        }

                        if (i == 2)
                        {
                            //dr["G"] = temp.Rows[i1]["日"].ToString();
                            dr["H"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["I"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["J"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["K"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["L"] = temp.Rows[i1]["总里程"].ToString();
                        }

                        if (i == 3)
                        {
                            //dr["M"] = temp.Rows[i1]["日"].ToString();
                            dr["N"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["O"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["P"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["Q"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["R"] = temp.Rows[i1]["总里程"].ToString();
                        }
                        if (i == 4)
                        {
                            dr["S"] = temp.Rows[i1]["日"].ToString();
                            dr["T"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["U"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["V"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["W"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["X"] = temp.Rows[i1]["总里程"].ToString();
                        }
                        if (i == 5)
                        {
                            //dr["Y"] = temp.Rows[i1]["日"].ToString();
                            dr["Z"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["A1"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["B1"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["C1"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["D1"] = temp.Rows[i1]["总里程"].ToString();
                        }
                        if (i == 6)
                        {
                            //dr["E1"] = temp.Rows[i1]["日"].ToString();
                            dr["F1"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["G1"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["H1"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["I1"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["J1"] = temp.Rows[i1]["总里程"].ToString();
                        }
                        if (i == 7)
                        {
                            dr["K1"] = temp.Rows[i1]["日"].ToString();
                            dr["L1"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["M1"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["N1"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["O1"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["P1"] = temp.Rows[i1]["总里程"].ToString();
                        }

                        if (i == 8)
                        {
                            //dr["Q1"] = temp.Rows[i1]["日"].ToString();
                            dr["R1"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["S1"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["T1"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["U1"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["V1"] = temp.Rows[i1]["总里程"].ToString();
                        }

                        if (i == 9)
                        {
                            //dr["W1"] = temp.Rows[i1]["日"].ToString();
                            dr["X1"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["Y1"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["Z1"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["A2"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["B2"] = temp.Rows[i1]["总里程"].ToString();
                        }

                        if (i == 10)
                        {
                            dr["C2"] = temp.Rows[i1]["日"].ToString();
                            dr["D2"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["E2"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["F2"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["G2"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["H2"] = temp.Rows[i1]["总里程"].ToString();
                        }
                        if (i == 11)
                        {
                            //dr["I2"] = temp.Rows[i1]["日"].ToString();
                            dr["J2"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["K2"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["L2"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["M2"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["N2"] = temp.Rows[i1]["总里程"].ToString();
                        }
                        if (i == 12)
                        {
                            //dr["O2"] = temp.Rows[i1]["日"].ToString();
                            dr["P2"] = temp.Rows[i1]["一月份日报公里数"].ToString();
                            dr["Q2"] = temp.Rows[i1]["一月份周期间隔公里"].ToString();
                            dr["R2"] = temp.Rows[i1]["一月份进厂路码"].ToString();
                            dr["S2"] = temp.Rows[i1]["一月份下班路码"].ToString();
                            dr["T2"] = temp.Rows[i1]["总里程"].ToString();

                        }
                    }
                }

                total.Rows.Add(dr);
            }
            return total;
        }


        //获取车管科保养管理的所有，根据车牌号，年份，月份
        public DataTable getCGKMaintainPlan(string ambNumber, string year, string month)
        {
            //去年
            string lastyear = Convert.ToString(int.Parse(year) - 1);
            StringBuilder sb = new StringBuilder();
            sb.Append(@" 
              create table #temp([序号][varchar](21),[日] [varchar](16) NOT NULL)
	            insert into #temp 
	            values('1','1')
	            insert into #temp
	            values('2','2')
	            insert into #temp
	            values('3','3')
	            insert into #temp
	            values('4','4')
	            insert into #temp
	            values('5','5')
	            insert into #temp
	            values('6','6')
	            insert into #temp
	            values('7','7')
	            insert into #temp
	            values('8','8')
	            insert into #temp
	            values('9','9')
	            insert into #temp
	            values('10','10')
	            insert into #temp
	            values('11','11')
	            insert into #temp
	            values('12','12')
				  insert into #temp
	            values('13','13')
				  insert into #temp
	            values('14','14')
				  insert into #temp
	            values('15','15')
				  insert into #temp
	            values('16','16')
				  insert into #temp
	            values('17','17')
				  insert into #temp
	            values('18','18')
				  insert into #temp
	            values('19','19')
				  insert into #temp
	            values('20','20')
				  insert into #temp
	            values('21','21')
				  insert into #temp
	            values('22','22')
				  insert into #temp
	            values('23','23')
				  insert into #temp
	            values('24','24')
				  insert into #temp
	            values('25','25')
				  insert into #temp
	            values('26','26')
				  insert into #temp
	            values('27','27')
				  insert into #temp
	            values('28','28')
				  insert into #temp
	            values('29','29')
				  insert into #temp
	            values('30','30')
				  insert into #temp
	            values('31','31')

			--总里程
			select    sum(dr1.DayKM)  as 总里程  into  #temp1  from   D_DriverReport  dr1  where  dr1.AmbulanceNumber = '" + ambNumber + @"'
			--总里程中当月最后一次出车的日期
			select top 1  datepart(day,dr2.ReportDate)  as redate   into #temp2  from    D_DriverReport  dr2  where  dr2.AmbulanceNumber = '" + ambNumber + @"'  and  datepart(year,ReportDate)  = '" + year + @"' and datepart(month,ReportDate)  ='" + month + @"' 
			order by  dr2.ReportDate  desc 

			--周期间隔公里和进厂路码
			--如果一个月里有2个进厂日期就取进厂路码大的减去进厂路码小的
			select    case when  count(datepart(day,rm.SendConfirmedTime)) >1   then   max(rm.InMileage)- min(rm.InMileage)
			--进厂路码减去 （上一次最近的进厂时间的进厂路码）
			else  (  rm.InMileage  - (	select top 1 rm1.InMileage  from  C_RepairMaintain  rm1 
			where rm1.ActualCode = '" + ambNumber + @"' and 
			rm1.SendConfirmedTime <(select rm2.SendConfirmedTime from C_RepairMaintain  rm2 
			where rm2.ActualCode = '" + ambNumber + @"' and 
			datepart(year,rm2.SendConfirmedTime )  = '" + year + @"' and datepart(month,rm2.SendConfirmedTime )  ='" + month + @"' and rm2.RepairType='保养'  )  and rm1.RepairType='保养'
			order  by rm1.SendConfirmedTime desc )) end  as  一月份周期间隔公里,  
			rm.InMileage  as 一月份进厂路码,
			datepart(day,rm.SendConfirmedTime ) as 一月份周期日
			into #temp3
			from C_RepairMaintain  rm 
			where rm.ActualCode = '" + ambNumber + @"' and 
			datepart(year,rm.SendConfirmedTime )  = '" + year + @"' and datepart(month,rm.SendConfirmedTime )  = '" + month + @"'  and rm.RepairType='保养' 
			group by rm.InMileage ,datepart(day,rm.SendConfirmedTime ) 

			--取全部合在一起
			select t.日,A.一月份日报公里数,D.一月份周期间隔公里,D.一月份进厂路码,A.一月份下班路码,C.总里程
			from  #temp t
			--公里数和下班路码
			left join (
			select    distinct datepart(day,ReportDate) as 一月份日期, sum(dr.DayKM) as 一月份日报公里数    
			,max(OffDutyKM)   as  一月份下班路码 
			FROM D_DriverReport  dr 
			where dr.AmbulanceNumber = '" + ambNumber + @"' and datepart(year,ReportDate)  = '" + year + @"' and datepart(month,ReportDate)  ='" + month + @"'
			group by datepart(day,ReportDate)
			) A  on A.一月份日期 = t.日
			
			--总里程 来自临时表
			left join (
			select  总里程,redate     from #temp1,#temp2 
			) C on C.redate = t.日
			--周期间隔公里,进厂路码 来自临时表
			left join (
			select 一月份周期间隔公里,一月份进厂路码,一月份周期日 from  #temp3 
			)D  on D.一月份周期日 = t.日

			drop table  #temp,#temp1,#temp2,#temp3   ");




            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }

        //获取整年续前二保后累计里程
        public DataTable GETYearXEBLJLC(string ambNumber, string year)
        {

            DataTable total = new DataTable();
            total.Columns.Add("A");
            total.Columns.Add("B");
            total.Columns.Add("C");
            total.Columns.Add("D");
            total.Columns.Add("E");
            total.Columns.Add("F");
            total.Columns.Add("G");
            total.Columns.Add("H");
            total.Columns.Add("I");
            total.Columns.Add("J");
            total.Columns.Add("K");
            total.Columns.Add("L");
            DataRow dr = total.NewRow();

            for (int i = 1; i < 13; i++)
            {
                string ii = i.ToString();
                DataTable temp = GETMonthXEBLJLC(ambNumber, year, ii);
                if (i == 1)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["A"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }

                }
                if (i == 2)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["B"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 3)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["C"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();

                    }
                }
                if (i == 4)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["D"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 5)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["E"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();

                    }
                }
                if (i == 6)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["F"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 7)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["G"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 8)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["H"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 9)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["I"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 10)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["J"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 11)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["K"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }
                if (i == 12)
                {
                    if (temp.Rows.Count > 0)
                    {
                        dr["L"] = temp.Rows[0]["一月份续前二保累计里程"].ToString();
                    }
                }

            }
            total.Rows.Add(dr);

            return total;
        }

//        public DataTable GETMonthXEBLJLC(string ambNumber, string year, string month)
//        {
//            //去年
//            string lastyear = Convert.ToString(int.Parse(year) - 1);
//            StringBuilder sb = new StringBuilder();
//            sb.Append(@"	--续前二保后累计里程
//			select case when 
//			--先判断本月是否有进厂路码
//			(select  count(rm.InMileage)   
//			from C_RepairMaintain  rm   
//			where rm.ActualCode = '" + ambNumber + @"' and 
//			datepart(year,rm.SendConfirmedTime )  = '" + year + @"' and datepart(month,rm.SendConfirmedTime )  ='" + month + @"'  and rm.RepairType='保养')>0 
//			--如果有就取日报的里程合计，里程合计日期是算进厂路码后一天到月底的里程合
//			then  (select sum(drz.DayKM)  
//			from D_DriverReport drz 
//			where drz.ReportDate >(select  top 1
//			CONVERT(varchar, rmz.SendConfirmedTime, 120)
//			from C_RepairMaintain  rmz   
//			where rmz.ActualCode = '" + ambNumber + @"' and 
//			datepart(year,rmz.SendConfirmedTime )  = '" + year + @"' and datepart(month,rmz.SendConfirmedTime )  ='" + month + @"'  and rmz.RepairType='保养' order by rmz.SendConfirmedTime desc   )  
//			and  datepart(month,drz.ReportDate) = '" + month + @"' and datepart(year,drz.ReportDate )  = '" + year + @"' and drz.AmbulanceNumber   = '" + ambNumber + @"')
//	
//			--如果没有就取上个月的里程合计
//			else  (  case when datepart(month,dr.ReportDate) = '1'     then 
//			( select    sum(drf.DayKM)   from    D_DriverReport drf  where datepart(month,drf.ReportDate) = '12' and datepart(year,drf.ReportDate )  = '" + lastyear + @"'  )
//			else sum(dr.DayKM)  end 
//			)  end    as  一月份续前二保累计里程  from  D_DriverReport  dr  where dr.AmbulanceNumber = '" + ambNumber + @"' and 
//			datepart(year,dr.ReportDate )  = '" + year + @"'  and  datepart(month,dr.ReportDate) = '" + month + @"' 
//
//			group by   datepart(month,dr.ReportDate)    ");
//            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
//            return ds.Tables[0];
//        }


        /// <summary>
        /// 新取法，8月就取8月前的最近一次保养日期到8月最后一次填写日报的区间的 里程的合计 2016-09-22 zch
        /// </summary>
        /// <param name="ambNumber"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GETMonthXEBLJLC(string ambNumber, string year, string month) 
        {
            //去年 
            string lastyear = Convert.ToString(int.Parse(year) - 1);
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
		select sum(drz.DayKM) as 一月份续前二保累计里程 
			from D_DriverReport drz 
			where drz.ReportDate >(select  max(
			CONVERT(varchar, rmz.SendConfirmedTime, 120))
			from C_RepairMaintain  rmz   
			where rmz.ActualCode = '" + ambNumber + @"' and 
			datepart(year,rmz.SendConfirmedTime )  = '" + year + @"' and datepart(month,rmz.SendConfirmedTime )  <='" + month + @"'  and rmz.RepairType='保养'   
			and  datepart(month,drz.ReportDate) <= '" + month + @"' and datepart(year,drz.ReportDate )  = '" + year + @"' and drz.AmbulanceNumber   = '" + ambNumber + @"')
	   ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }


        #endregion



        #region 续前2保后累计里程 1 修改
        public DataTable GETMonthXEBLJLC1(string ambNumber, string year, string month)
        {
            string lastyear = Convert.ToString(int.Parse(year) - 1);
            string lastmonth = Convert.ToString(int.Parse(month) - 1);
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
            select  count(rm.InMileage)   
			from C_RepairMaintain  rm   
			where rm.AmbulanceNumber = '" + ambNumber + @"' and 
			datepart(year,rm.SendConfirmedTime )  = '" + year + @"' and datepart(month,rm.SendConfirmedTime )  ='" + lastmonth + @"'  and rm.RepairType='保养'

                        ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            int temp1 = Convert.ToInt32(ds.Tables[0]);
            if (temp1 > 0)
                sb.Append(@"
                  select sum(drz.DayKM)  
			from D_DriverReport drz 
			where drz.ReportDate >(select
			CONVERT(varchar, rmz.SendConfirmedTime, 120)
			from C_RepairMaintain  rmz   
			where rmz.AmbulanceNumber = '" + ambNumber + @"' and 
			datepart(year,rmz.SendConfirmedTime )  = '" + year + @"' and datepart(month,rmz.SendConfirmedTime )  ='" + month + @"'  and rmz.RepairType='保养' )  
			and  datepart(month,drz.ReportDate) = '" + month + @"' and datepart(year,drz.ReportDate )  = '" + year + @"'   ");


            DataSet ds1 = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds1.Tables[0];

        }
        #endregion




        #region    车辆保养计划提醒页面获取数据
        public object getAllambMaintain(ref int totals, DateTime beginTime, DateTime endTime, string DayKM)
        {
            StringBuilder sb = new StringBuilder();

            //先去车辆表取出所有车辆信息
            sb.Append(@"
                    select  ca.AmbulanceCode,ca.PlateNumber,ca.CarNumber   from  C_Ambulance ca  where ca.isActive = 1  
                    select count(AmbulanceCode) as times   from  C_Ambulance  ca  where ca.isActive = 1  
                    ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            int rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            DataTable total = new DataTable();
            //total.Columns.Add("HangHao"); //行号
            total.Columns.Add("Center");
            total.Columns.Add("Station");
            total.Columns.Add("ActualCode");
            total.Columns.Add("CarNumber");
            total.Columns.Add("SendConfirmedTime");
            total.Columns.Add("RepairContent");
            total.Columns.Add("DayKM");

            //一共多少辆车
            for (int i1 = 0; i1 < rowtotal; i1++)
            {
                //新一行
                DataRow dr = total.NewRow();
                string tempambNumber = Convert.ToString(ds.Tables[0].Rows[i1][1]);
                DataTable temp = getOneMaintain(tempambNumber);
                if (temp.Rows.Count > 0)
                {
                    string a = temp.Rows[0]["Center"].ToString();

                    dr["Center"] = temp.Rows[0]["Center"].ToString();
                    dr["Station"] = temp.Rows[0]["Station"].ToString();
                    dr["ActualCode"] = temp.Rows[0]["ActualCode"].ToString();
                    dr["CarNumber"] = temp.Rows[0]["CarNumber"].ToString();
                    dr["SendConfirmedTime"] = temp.Rows[0]["SendConfirmedTime"].ToString();//保养时间
                    dr["RepairContent"] = temp.Rows[0]["RepairContent"].ToString(); //保养内容
                    dr["DayKM"] = temp.Rows[0]["DayKM"].ToString(); //保养后行驶公里
                    //列 12 
                    total.Rows.Add(dr);
                }
            }

            //这里使用了 在C#中要对Datatable排序，可使用DefaultView的Sort方法。先获取Datatable的DefaultView，然后设置得到的Dataview的sort属性，最后用视图的ToTable方法将排好序的dataview导出为Datatable。
            //DataTable dtCopy = total.Copy();
            //DataView dv = total.DefaultView;
            //dv.Sort = "DayKM  desc";
            //dtCopy = dv.ToTable();

            DataTable newdt = new DataTable();
            newdt = total.Clone();
            if (DayKM.Length > 0)
            {
                //2016-10-10 zch 修改 筛选方法，table 的 Select 时间10月出错
                DataRow[] rows = total.Select(" DayKM>='" + DayKM + "' ");
                foreach (DataRow row in rows)  // 将查询的结果添加到table中； 
                {
                    if (beginTime <= Convert.ToDateTime(row["SendConfirmedTime"]) && Convert.ToDateTime(row["SendConfirmedTime"]) < endTime)
                        newdt.Rows.Add(row.ItemArray);
                }
            }
            else
            {
                //2016-10-10 zch 修改 筛选方法，table 的 Select 时间10月出错
                foreach (DataRow row in total.Rows)  // 将查询的结果添加到table中； 
                {
                    if (beginTime <= Convert.ToDateTime(row["SendConfirmedTime"]) && Convert.ToDateTime(row["SendConfirmedTime"]) < endTime)
                        newdt.Rows.Add(row.ItemArray);
                }
            }
            //这里使用了 在C#中要对Datatable排序，可使用DefaultView的Sort方法。先获取Datatable的DefaultView，然后设置得到的Dataview的sort属性，最后用视图的ToTable方法将排好序的dataview导出为Datatable。
            DataTable dtCopy = newdt.Copy();
            DataView dv = newdt.DefaultView;
            dv.Sort = "DayKM  desc";
            dtCopy = dv.ToTable();


            List<MaintainPlanListInfo> list = new List<MaintainPlanListInfo>();
            if (total.Rows.Count > 0)
            {
                foreach (DataRow dr in dtCopy.Rows)
                {
                    MaintainPlanListInfo info = new MaintainPlanListInfo();
                    info.CenterCode = dr["Center"].ToString();
                    info.StationCode = dr["Station"].ToString();
                    info.ActualCode = dr["ActualCode"].ToString();
                    info.CarNumber = dr["CarNumber"].ToString();
                    info.SendConfirmedTime = dr["SendConfirmedTime"].ToString();
                    info.RepairContent = dr["RepairContent"].ToString();
                    info.DayKM = dr["DayKM"].ToString();

                    list.Add(info);
                }
            }
            totals = Convert.ToInt32(total.Rows.Count);
            return list;

        }

        //车辆保养计划的打印
        public DataTable getAllambMaintainPrint(DateTime beginTime, DateTime endTime, string DayKM)
        {
            StringBuilder sb = new StringBuilder();

            //先去车辆表取出所有车辆信息
            sb.Append(@"
                
                    select  ca.AmbulanceCode,ca.PlateNumber,ca.CarNumber   from  C_Ambulance ca  where ca.isActive = 1  
                    select count(AmbulanceCode) as times   from  C_Ambulance  ca  where ca.isActive = 1  
                    ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            int rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            DataTable total = new DataTable();
            //total.Columns.Add("HangHao"); //行号
            total.Columns.Add("Center");
            total.Columns.Add("Station");
            total.Columns.Add("ActualCode");
            total.Columns.Add("CarNumber");
            total.Columns.Add("SendConfirmedTime");
            total.Columns.Add("RepairContent");
            total.Columns.Add("DayKM");

            //一共多少辆车
            for (int i1 = 0; i1 < rowtotal; i1++)
            {
                //新一行
                DataRow dr = total.NewRow();
                string tempambNumber = Convert.ToString(ds.Tables[0].Rows[i1][1]);
                DataTable temp = getOneMaintain(tempambNumber);
                if (temp.Rows.Count > 0)
                {
                    string a = temp.Rows[0]["Center"].ToString();
                    dr["Center"] = temp.Rows[0]["Center"].ToString();
                    dr["Station"] = temp.Rows[0]["Station"].ToString();
                    dr["ActualCode"] = temp.Rows[0]["ActualCode"].ToString();
                    dr["CarNumber"] = temp.Rows[0]["CarNumber"].ToString();
                    dr["SendConfirmedTime"] = temp.Rows[0]["SendConfirmedTime"].ToString();//保养时间
                    dr["RepairContent"] = temp.Rows[0]["RepairContent"].ToString(); //保养内容
                    dr["DayKM"] = temp.Rows[0]["DayKM"].ToString(); //保养后行驶公里
                    //列 12 
                    total.Rows.Add(dr);
                }
            }

            DataTable newdt = new DataTable();
            newdt = total.Clone();
            if (DayKM.Length > 0)
            {  //2016-10-10 zch 修改 筛选方法，table 的 Select 时间10月出错
                DataRow[] rows = total.Select(" DayKM>='" + DayKM + "' ");
                foreach (DataRow row in rows)  // 将查询的结果添加到table中； 
                {
                    if (beginTime <= Convert.ToDateTime(row["SendConfirmedTime"]) && Convert.ToDateTime(row["SendConfirmedTime"]) < endTime)
                        newdt.Rows.Add(row.ItemArray);
                }
            }
            else
            {
                foreach (DataRow row in total.Rows)  // 将查询的结果添加到table中； 
                {
                    if (beginTime <= Convert.ToDateTime(row["SendConfirmedTime"]) && Convert.ToDateTime(row["SendConfirmedTime"]) < endTime)
                        newdt.Rows.Add(row.ItemArray);
                }
            }

            //这里使用了 在C#中要对Datatable排序，可使用DefaultView的Sort方法。先获取Datatable的DefaultView，然后设置得到的Dataview的sort属性，最后用视图的ToTable方法将排好序的dataview导出为Datatable。
            DataTable dtCopy = newdt.Copy();
            DataView dv = newdt.DefaultView;
            dv.Sort = "DayKM  desc";
            dtCopy = dv.ToTable();
            return dtCopy;
        }


        //传入车牌号码获取车辆保养表内容和保养后日报表行驶公里数
        public DataTable getOneMaintain(string ambNumber)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
             select   top 1     rm.AmbulanceCode,AmbulanceNumber,ActualCode,rm.AmbulanceNumber as CarNumber,SendConfirmedTime,RepairContent,
			 tc.名称 as Center,ts.名称 as Station
			  
				   into #temp1
			       from C_RepairMaintain rm ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on rm.CenterID = tc.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on rm.StationID = ts.编码 ");
            sb.Append(@"    where rm.ActualCode = '" + ambNumber + @"'   and rm.RepairType='保养' 
                   order by    rm.SendConfirmedTime    desc   

		     select  sum(isnull(drz.DayKM,0))   as  DayKM ,drz.AmbulanceNumber  
                    into #temp2
        			from D_DriverReport drz 
        			where drz.ReportDate >(select  top 1 
        			CONVERT(varchar, rmz.SendConfirmedTime, 120)
        			from C_RepairMaintain  rmz   
        			where rmz.ActualCode = '" + ambNumber + @"' and  rmz.RepairType='保养'  order by rmz.SendConfirmedTime  desc   )  
        			and  drz.AmbulanceNumber ='" + ambNumber + @"'  
					group by drz.AmbulanceNumber

				select * from #temp1  t1 
				 left join  #temp2 t2 on t1.ActualCode=t2.AmbulanceNumber

				drop table  #temp1,#temp2   ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }

        #endregion



    }
}
